﻿using System;
using WorkOrder.Model;

namespace WorkOrder.Business
{
    public class Business_Table_Order_record
    {
        DbHelper dbhelper = new DbHelper();

        #region 保存
        public bool Insert(Model_Table_Order_record model)
        {
            string sqlstr = "";
            if (!string.IsNullOrEmpty(model.Response_time))
                sqlstr = " declare @return_value int; exec @return_value=UP_InsertRecord '" + model.Order_id + "','" + model.Order_number + "'," +
                   model.Process_id + "," + model.Dept_id + "," + model.Step_id + ",'" + model.Request_time.ToString() + "','" + model.Response_time.ToString() + "','" +
                   model.Operator + "'," + model.Entry_person + ",'" + model.Remark + "';select 'return_value'=@return_value;";
            else
                sqlstr = " declare @return_value int; exec @return_value=UP_InsertRecord '" + model.Order_id + "','" + model.Order_number + "'," +
                   model.Process_id + "," + model.Dept_id + "," + model.Step_id + ",'" + model.Request_time.ToString() + "',NULL,'" +
                   model.Operator + "'," + model.Entry_person + ",'" + model.Remark + "';select 'return_value'=@return_value;";
            if (Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr)) == 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 更新
        public bool Update(Model_Table_Order_record model)
        {
            string sqlstr = "";
            if (!string.IsNullOrEmpty(model.Response_time))
                sqlstr = " declare @return_value int; exec @return_value=UP_UpdateRecord '" + model.Order_record_id + "'," +
                   model.Process_id + "," + model.Dept_id + "," + model.Step_id + ",'" + model.Request_time.ToString() + "','" + model.Response_time.ToString() + "','" +
                   model.Operator + "'," + model.Entry_person + ",'" + model.Remark + "';select 'return_value'=@return_value;";
            else
                sqlstr = " declare @return_value int; exec @return_value=UP_UpdateRecord '" + model.Order_record_id + "'," +
                   model.Process_id + "," + model.Dept_id + "," + model.Step_id + ",'" + model.Request_time.ToString() + "',NULL,'" +
                   model.Operator + "'," + model.Entry_person + ",'" + model.Remark + "';select 'return_value'=@return_value;";
            if (Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr)) == 0)
                return true;
            else
                return false;
        }
        #endregion
        
        #region 得到单个数据集
        public Model_Table_Order_record GetData(string keyValue)
        {
            string sql = "select A.*, B.order_number,C.process,D.dept_name,E.remark as entry_person_name,F.step_name from Table_Order_record as A " +
                "inner join Table_Work_order as B on A.order_id=B.order_id " +
                "inner join Table_Process as C on A.process_id=C.process_id " +
                "inner join Table_Step as F on A.step_id=F.step_id " +
                "left join Table_Department as D on A.dept_id=D.dept_id " +
                "left join Table_User as E on A.entry_person=E.user_id " +
                "where A.order_record_id='" + keyValue + "'";

            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sql))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Order_record tmp;
                    string order_record_id = "", order_id = "", request_time = "", response_time = "", _operator = "", remark = "", order_number = "", process = "", entry_person_name = "";
                    string process_id = "", entry_person = "", dept_id = "", dept_name="", step_id="";
                    decimal process_time = 0;
                    if (!DBNull.Value.Equals(rdr["response_time"]))
                        response_time = rdr["response_time"].ToString();
                    if (!DBNull.Value.Equals(rdr["order_record_id"]))
                        order_record_id = rdr["order_record_id"].ToString();
                    if (!DBNull.Value.Equals(rdr["order_id"]))
                        order_id = rdr["order_id"].ToString();
                    if (!DBNull.Value.Equals(rdr["request_time"]))
                        request_time = rdr["request_time"].ToString();
                    if (!DBNull.Value.Equals(rdr["entry_person_name"]))
                        entry_person_name = rdr["entry_person_name"].ToString();
                    if (!DBNull.Value.Equals(rdr["operator"]))
                        _operator = rdr["operator"].ToString();
                    if (!DBNull.Value.Equals(rdr["remark"]))
                        remark = rdr["remark"].ToString();
                    if (!DBNull.Value.Equals(rdr["order_number"]))
                        order_number = rdr["order_number"].ToString();
                    if (!DBNull.Value.Equals(rdr["process"]))
                        process = rdr["process"].ToString();
                    if (!DBNull.Value.Equals(rdr["dept_name"]))
                        dept_name = rdr["dept_name"].ToString();
                    if (!DBNull.Value.Equals(rdr["process_id"]))
                        process_id = rdr["process_id"].ToString();
                    if (!DBNull.Value.Equals(rdr["entry_person"]))
                        entry_person = rdr["entry_person"].ToString();
                    if (!DBNull.Value.Equals(rdr["dept_id"]))
                        dept_id = rdr["dept_id"].ToString();
                    if (!DBNull.Value.Equals(rdr["step_id"]))
                        step_id = rdr["step_id"].ToString();
                    rdr.Close();
                    tmp = new Model_Table_Order_record(order_record_id, order_id, process_id,
                            request_time, response_time, process_time,
                            _operator, entry_person, remark, dept_id, step_id);
                    tmp.Process_name = process;
                    tmp.Order_number = order_number;
                    tmp.Dept_name = dept_name;
                    tmp.Entry_person_name = entry_person_name;
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else return null;
            }
        }
        #endregion

        #region 删除记录
        public bool Delete(string fieldName, string fieldValue)
        {
            string sqlstr = "delete Table_Order_record where " + fieldName + "='" + fieldValue + "'";
            if (dbhelper.ExecuteNoQuery(sqlstr) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 返回流程资料
        public System.Data.DataSet GetProcess()
        {
            string sqlstr = "select process_id,process from Table_Process where process<>'已完成' and process<>'[空]'";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 检查工单是否已存在
        public bool IsExist(string keyValue)
        {
            string sqlstr = "select top 1 order_number from Table_Work_order where order_number='" + keyValue + "'";
            return dbhelper.ExecuteScalar(sqlstr) != null;
        }
        #endregion
        
        #region 检查流程在流程表中是否存在
        /// <summary>
        /// 检查流程在流程表中是否存在
        /// </summary>
        /// <param name="proc_name">流程名称</param>
        /// <returns></returns>
        public bool ProcIsExistInTable(string proc_name)
        {
            string sqlstr = "select top 1 process_id from Table_Process where process='" + proc_name + "'";
            return dbhelper.ExecuteScalar(sqlstr) != null;
        }
        #endregion

        #region 从excel文件导入数据
        public object InsertDataFromExcel(string[] sqlstr, string fileName)
        {
            ExcelHelper excelHelper = new ExcelHelper(fileName);
            return excelHelper.ImportToDB(sqlstr);
        }
        #endregion

        #region 简单查询数据记录
        public System.Data.DataSet GetRecord(string orderID)
        {
            string sqlstr = @"exec UP_GetRecord '" + orderID + "'";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据orderid返回工单号
        public string GetOrderNumberByID(string orderid)
        {
            string ordernumber = "";
            string sqlstr = "select order_number from Table_Work_order where order_id='" + orderid + "'";
            object tmp = dbhelper.ExecuteScalar(sqlstr);
            if (tmp != null) ordernumber = tmp.ToString();
            return ordernumber;
        }
        #endregion

        #region 根据orderid返回工单号
        public string GetOrderIdByNumber(string orderNumber)
        {
            string orderID = "";
            string sqlstr = "select order_id from Table_Work_order where order_number='" + orderNumber + "'";
            object tmp = dbhelper.ExecuteScalar(sqlstr);
            if (tmp != null) orderID = tmp.ToString();
            return orderID;
        }
        #endregion

        #region 返回部门资料
        public System.Data.DataSet GetDept()
        {
            string sqlstr = "select dept_id,dept_name from Table_Department where dept_name<>'[空]'";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回环节资料
        public System.Data.DataSet GetStep()
        {
            string sqlstr = "select step_id,step_name from Table_Step where step_name<>'[空]' and step_name<>'完成'";
            return dbhelper.Query(sqlstr);
        }
        #endregion
    }
}
